﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace HRdex_Final.Forms
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        DataTable dt = new DataTable("dt");

        //Session String
        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }



        protected void Page_Load(object sender, EventArgs e)
        {
            ddlStatus.Visible = false;
            
            SqlCommand cmd3 = new SqlCommand("SELECT EmployeeID, LastName + ', ' + FirstName + ' ' + MiddleName AS Name," +
                "catPositionsID, DeptID, CollegeID FROM formEmployees WHERE (EmployeeID=" + SessionID() + ")", conn);
            SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
            DataTable dt3 = new DataTable("dt3");

            da3.Fill(dt3);

            txtEmpNo.Text = dt3.Rows[0].ItemArray[0].ToString();
            txtEmpName.Text = dt3.Rows[0].ItemArray[1].ToString();
            ddlDept.SelectedValue = dt3.Rows[0].ItemArray[3].ToString();
            ddlCollege.SelectedValue = dt3.Rows[0].ItemArray[4].ToString();
            txtEmpNo.Enabled = false;
            txtEmpName.Enabled = false;
            ddlDept.Enabled = false;
            ddlCollege.Enabled = false;
            lblSig.Visible = false;
            txtCBAid.Visible = false;
            lblDate.Text = "Date";
            lblDateResult.Text = Convert.ToString(DateTime.Now);
            
            Selected();



        }

        //Submit
        protected void btnSubmit_Click(object sender, EventArgs e)
        {
            string formCBAID = Request.QueryString["formCBAID"];

            if (txtCBAid.Text != formCBAID)
            {
                try
                {
                   
                    SqlCommand cmdd = new SqlCommand("INSERT Into formCBA (StudentNo, DateSubmitted, CourseID, Year, StudentName, " +
                        "EmployeeID, DepartmentID, CollegeID,  pDiscount, OrdinalPos,Cleared,Enrollment, Units, AttachmentData )" +
                        "VALUES (@sNo,@date,@courseID, @year, @sName,@empID,@deptID, @collegeID, @pDiscount, @posID, @cleared,@enrollment, " +
                        "@units, @data)", conn);

                    Stream fs = fuAttachment.PostedFile.InputStream;
                    BinaryReader br = new BinaryReader(fs);
                    Byte[] bytes = br.ReadBytes((Int32)fs.Length);

                    SqlDataAdapter daa = new SqlDataAdapter(cmdd);

                    conn.Open();
                    cmdd.Parameters.Add("@sNo", SqlDbType.Int).Value = txtStudentNo.Text;
                    cmdd.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Now;
                    cmdd.Parameters.Add("@courseID", SqlDbType.Int).Value = dllCourse.SelectedValue;
                    cmdd.Parameters.Add("@year", SqlDbType.Int).Value = ddlYear.SelectedValue;
                    cmdd.Parameters.Add("@sName", SqlDbType.VarChar).Value = txtFullName.Text;
                    cmdd.Parameters.Add("@empID", SqlDbType.Int).Value = txtEmpNo.Text;
                    cmdd.Parameters.Add("@deptID", SqlDbType.Int).Value = ddlDept.SelectedValue;
                    cmdd.Parameters.Add("@collegeID", SqlDbType.Int).Value = ddlCollege.SelectedValue;
                    cmdd.Parameters.Add("@pDiscount", SqlDbType.VarChar).Value = txtDiscount.Text;
                    cmdd.Parameters.Add("@posID", SqlDbType.Int).Value = txtOrdinalPos.Text;
                    cmdd.Parameters.Add("@cleared", SqlDbType.VarChar).Value = txtCleared.Text;
                    cmdd.Parameters.Add("@enrollment", SqlDbType.VarChar).Value = txtEnrollment.Text;
                    cmdd.Parameters.Add("@units", SqlDbType.VarChar).Value = txtUnits.Text;
                    cmdd.Parameters.Add("@data", SqlDbType.VarBinary).Value = bytes;

                    cmdd.ExecuteNonQuery();

                    lblConfirmation.Text = "You successfully add new CBA request. Please wait upon your approval upon reviewing your application. Thank you!";
                    
                    txtStudentNo.Enabled = false;
                    dllCourse.Enabled = false;
                    ddlYear.Enabled = false;
                    txtFullName.Enabled = false;
                    txtOrdinalPos.Enabled = false;
                    txtCleared.Enabled = false;
                    txtEnrollment.Enabled = false;
                    txtUnits.Enabled = false;
                   
                }

                catch (Exception)
                {
 
                    throw;
                }
            }
            else
            {
                Edit();
                notification();
            }
        }

        public void Selected()
        {
            string formCBAID = Request.QueryString["formCBAID"];

            if (formCBAID != null)
            {

                txtCBAid.Enabled = false;
                txtCleared.Enabled = false;
                txtEmpNo.Enabled = false;
                txtEnrollment.Enabled = false;
                txtFullName.Enabled = false;
                txtDiscount.Enabled = false;
                ddlStatus.Enabled = false;
                txtStudentNo.Enabled = false;
                txtUnits.Enabled = false;
                dllCourse.Visible = false;
                ddlStatus.Enabled = false;
                ddlYear.Enabled = false;


                SqlCommand cmd5 = new SqlCommand("SELECT formCBA.formCBAID, formCBA.StudentNo, formCBA.DateSubmitted,"+
                    "catCourse.CourseName, formCBA.Year, formCBA.StudentName, formCBA.pDiscount,"+
                    "formCBA.Cleared, formCBA.Enrollment, formCBA.Units, formEmployees.EmployeeID, "+
                    "formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name, "+
                    "formCBA.DepartmentID, formCBA.CollegeID, formCBA.OrdinalPos, formCBA.AttachmentName,  formCBA.HRApproval FROM formCBA INNER JOIN catCourse ON formCBA.CourseID = catCourse.CourseID INNER JOIN formEmployees ON formCBA.EmployeeID = formEmployees.EmployeeID INNER JOIN catDepartment ON formCBA.DepartmentID = catDepartment.DeptID INNER JOIN catCollege ON formCBA.CollegeID = catCollege.CollegeID WHERE (formCBA.formCBAID =" + formCBAID + ")", conn);
                SqlDataAdapter da5 = new SqlDataAdapter(cmd5);
                DataTable dt5 = new DataTable("dt5");
                da5.Fill(dt5);

                txtCBAid.Text = dt5.Rows[0].ItemArray[0].ToString();
                txtStudentNo.Text = dt5.Rows[0].ItemArray[1].ToString();
                lblDateResult.Text = dt5.Rows[0].ItemArray[2].ToString();
                dllCourse.SelectedValue = dt5.Rows[0].ItemArray[3].ToString();
                ddlYear.SelectedValue = dt5.Rows[0].ItemArray[4].ToString();
                txtFullName.Text = dt5.Rows[0].ItemArray[5].ToString();
                txtDiscount.Text = dt5.Rows[0].ItemArray[6].ToString();
                txtCleared.Text = dt5.Rows[0].ItemArray[7].ToString();
                txtEnrollment.Text = dt5.Rows[0].ItemArray[8].ToString();
                txtUnits.Text = dt5.Rows[0].ItemArray[9].ToString();
                txtEmpNo.Text = dt5.Rows[0].ItemArray[10].ToString();
                txtEmpName.Text = dt5.Rows[0].ItemArray[11].ToString();
                ddlDept.SelectedValue = dt5.Rows[0].ItemArray[12].ToString();
                ddlCollege.SelectedValue = dt5.Rows[0].ItemArray[13].ToString();
                txtOrdinalPos.Text = dt5.Rows[0].ItemArray[14].ToString();
                txtStatus.Text = dt5.Rows[0].ItemArray[16].ToString();
                txtStatus.Enabled = false;

                if (dt5.Rows[0].ItemArray[16].ToString() == "Approved")
                {
                    ddlStatus.SelectedValue = dt5.Rows[0].ItemArray[16].ToString();
                    
                    ddlStatus.Enabled = false;
                }
                else if (dt5.Rows[0].ItemArray[16].ToString() == "Denied")
                {
                    ddlStatus.SelectedValue = dt5.Rows[0].ItemArray[16].ToString();
                    
                    ddlStatus.Enabled = false;
                }
                else
                {
                    ddlStatus.Enabled = true;
                    
                }

                SqlCommand cmd2 = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName, " +
                    "catPositions.catPositionsName, catDepartment.DepartmentName FROM formEmployees INNER JOIN " +
                    "catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN " +
                    "catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID INNER JOIN catDepartment " +
                    "ON formEmployees.DeptID = catDepartment.DeptID WHERE (formEmployees.EmployeeID = " + SessionID() + ")", conn);
                SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
                DataTable dt2 = new DataTable("dt2");
                da2.Fill(dt2);

                
                

                if (dt2.Rows[0].ItemArray[1].ToString() == "Admin")
                {
                    ddlStatus.Visible = true;
                    txtStatus.Visible = false;
                }
                else if (dt2.Rows[0].ItemArray[1].ToString() == "SubAdmin" && dt2.Rows[0].ItemArray[2].ToString() == "Head" && dt2.Rows[0].ItemArray[3].ToString() == "HR Salaries and Benefits")
                {
                    ddlStatus.Visible = true;
                    txtStatus.Visible = false;
                }
                else
                {
                    ddlStatus.Visible = false;
                    txtStatus.Visible = true;
                }

            }


        }

        public void Edit()
        {
            string formCBAID = Request.QueryString["formCBAID"];
            SqlCommand cmd7 = new SqlCommand("UPDATE formCBA SET  HRApproval = @status WHERE(formCBAID = " + formCBAID + ")", conn);
            SqlDataAdapter da7 = new SqlDataAdapter(cmd7);
            conn.Open();

            cmd7.Parameters.AddWithValue("@status", SqlDbType.VarChar).Value = ddlStatus.SelectedValue;

            cmd7.ExecuteNonQuery();
            conn.Close();

            lblConfirmation.Text = "You successfully put remarks!";
            ddlStatus.Enabled = false;
        }



        void notification()
        {
            string formCBAID = Request.QueryString["formCBAID"];
            string desc = "Your request for CBA has been " + ddlStatus.Text + "";
            string sqlc = "INSERT INTO formMessegeSender " +
                "(Subject, Description, DateTime, EmployeeID, catMessegeID, fromEmpID, Readed) " +
                "VALUES ('[CBA Request]','" + desc + "','" + DateTime.Now + "'," + cbasender(formCBAID) +",2," +SessionID() +",0)";
            SqlCommand cmd = new SqlCommand(sqlc, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        string cbasender(string src)
        {
            string sqlx = "SELECT EmployeeID FROM formCBA WHERE formCBAID = " + src;
            SqlDataAdapter cmd = new SqlDataAdapter(sqlx, conn);
            DataTable tbl = new DataTable();
            
            conn.Open();
            cmd.Fill(tbl);
            conn.Close();

            return tbl.Rows[0][0].ToString();
        }

        protected void txtOrdinalPos_TextChanged(object sender, EventArgs e)
        {

            if (Convert.ToInt16(txtOrdinalPos.Text) <= 2 && Convert.ToInt16(txtOrdinalPos.Text) >= 0)
            {
                txtDiscount.Text = "100% Scholar";
            }
            else
            {
                txtDiscount.Text = "75% Scholar";
            }

        }


        protected void btnClear_Click(object sender, EventArgs e)
        {
            txtCBAid.Text = null;
            txtCleared.Text = null;
            txtDiscount.Text = null;
            txtEmpName.Text = null;
            txtEmpNo.Text = null;
            txtEnrollment.Text = null;
            txtFullName.Text = null;
            txtOrdinalPos.Text = null;
            txtStatus.Text = null;
            txtStudentNo.Text = null;
            txtUnits.Text = null;
        }
    }
}